<?php

require_once("lib/exception/duplicate_instance_exception.php");
require_once("lib/exception/internal_error_exception.php");
require_once("lib/exception/instance_not_found_exception.php");
require_once("lib/exception/sql_exception.php");

require_once("util/connection.php");

require_once("model/article_category/to/article_category_to.php");
require_once("model/article_category/dao/article_category_dao.php");


class Mysql_article_category_dao extends Article_category_dao {

	public function create($article_category_to) {
		if ($this->exist($article_category_to->get_number())) {
			throw new Duplicate_instance_exception(get_class($this));
		}
		try {
			$link = Connection::singleton()->transaction_begin();
	
			$title = mysql_real_escape_string($article_category_to->get_title());
			$description = mysql_real_escape_string($article_category_to->get_description());
			$autor_login = mysql_real_escape_string($article_category_to->get_autor_login());
			$ranking = $article_category_to->get_ranking();
			
			$result = mysql_query("insert into article_category	
									(title, description, autor_login, ranking) values 
									('$title', '$description', '$autor_login', '$ranking')",$link);
									
			if (!$result || (mysql_affected_rows($link) != 1) 
					|| (($number = mysql_insert_id($link)) == 0)) 
				throw new Sql_exception("Not row inserted in " . get_class($this));
			
			Connection::singleton()->transaction_commit($link);
			
			$article_category_to = new Article_category_to($number, $title, $description, 
															$autor_login, $ranking);
			return $article_category_to;
		}
		catch (Sql_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
		catch (Exception $e) {
			Connection::singleton()->transaction_rollback($link);
			throw new Exception("Internal error exception --> " . $e);
		}
	}
	
	public function exist($number){
		try {
			$link = Connection::singleton()->transaction_begin();
			
			$result = mysql_query("select * from article_category
									where number='$number'", $link);
									
			if (!$result || (mysql_num_rows($result) != 1)) {
				Connection::singleton()->transaction_commit($link);
				return false;								
			}

			Connection::singleton()->transaction_commit($link);
			return true;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
	}
	
	public function exist_by_title($title){
		try {
			$link = Connection::singleton()->transaction_begin();
			
			$title = mysql_real_escape_string($title);
			
			$result = mysql_query("select * from article_category
									where title like '$title'", $link);
									
			if (!$result || (mysql_num_rows($result) != 1)) {
				Connection::singleton()->transaction_commit($link);
				return false;								
			}

			Connection::singleton()->transaction_commit($link);
			return true;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
	}
	
	public function find_by_number_article_category($number) {
		try {
			$link = Connection::singleton()->transaction_begin();
			
			$result = mysql_query("select * from article_category
									where number='$number'", $link);
			
			if (mysql_num_rows($result) == 0) 
				throw new Instance_not_found_exception(get_class($this));
			
			if (mysql_num_rows($result) != 1) {
				throw new Sql_exception(get_class($this));
			}
			
			$aux = mysql_fetch_assoc($result);
			Connection::singleton()->transaction_commit($link);
			return new Article_category_to($aux['number'], $aux['title'], 
											$aux['description'],	$aux['autor_login'],
											$aux['ranking']);
			$aux = mysql_fetch_assoc($result);
		}
		catch (Sql_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e) {
			Connection::singleton()->transaction_rollback($link);
			throw new Exception("Internal error exception --> " . $e);
		}
	}	
	
	public function find_most_importants($number_categories) {
		try {
			$link = Connection::singleton()->transaction_begin();
			
			$result = mysql_query("select * from 
									(select * from article_category
									order by ranking desc
									limit $number_categories) AS partial
									order by partial.title", $link);	

			if (!$result) 
				throw new Instance_not_found_exception(get_class($this));
						
			$category_list = NULL;
			$i = 0;
			while ($aux = mysql_fetch_assoc($result))
				$category_list[$i++] = new Article_category_to($aux['number'], $aux['title'], 
											$aux['description'],	$aux['autor_login'],
											$aux['ranking']);
											
			Connection::singleton()->transaction_commit($link);
			return $category_list; 

		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
	}
	
	public function find_all() {
		try {
			$link = Connection::singleton()->transaction_begin();
			
			$result = mysql_query("select * from article_category
									order by ranking desc, title", $link);

			if (!$result) 
				throw new Instance_not_found_exception(get_class($this));
						
			$category_list = NULL;
			$i = 0;
			while ($aux = mysql_fetch_assoc($result))
				$category_list[$i++] = new Article_category_to($aux['number'], $aux['title'], 
											$aux['description'],	$aux['autor_login'],
											$aux['ranking']);
										
			Connection::singleton()->transaction_commit($link);
			return $category_list; 

		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}
	}
	
	public function count_categories_by_login($login) {
		try {	
			$link = Connection::singleton()->transaction_begin();

			$login = mysql_real_escape_string($login);
			
			$result = mysql_query("select COUNT(number) as count from article_category 
									where autor_login like '$login'", $link);
			
			if (!$result) 
				throw new Instance_not_found_exception(get_class($this));
				
			$aux = mysql_fetch_assoc($result);
			
			Connection::singleton()->transaction_commit($link);
									
			return $aux['count'];
						
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}	
	}
	
	
	public function update($article_category_to) {
		try {
			$link = Connection::singleton()->transaction_begin();
			
			$number = mysql_real_escape_string($article_category_to->get_number());
			$description = mysql_real_escape_string($article_category_to->get_description());
			$ranking = $article_category_to->get_ranking();			
			
			$result = mysql_query("update article_category set 
									description='$description',	ranking='$ranking'
									where number = '$number'", $link);
									
			if (!$result) 
				throw new Instance_not_found_exception(get_class($this));
			
			if (mysql_affected_rows($link) != 1) 
				throw new Sql_exception("Not row updated in " . get_class($this));
				
			Connection::singleton()->transaction_commit($link);
		}
		catch (Sql_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);		
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}	
	}
	
	public function remove($number) {
		try {
			 $link = Connection::singleton()->transaction_begin();
			 
			 $result = mysql_query("delete from article_category
			 						where number='$number'", $link);
			 
			if (mysql_affected_rows($link) == 0) 
				throw new Instance_not_found_exception(get_class($this));
			
			if (mysql_affected_rows($link) != 1) 
				throw new Sql_exception("Not row removed in " . get_class($this));
			
			Connection::singleton()->transaction_commit($link);
			
		}
		catch (Sql_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);		
		}
		catch (Instance_not_found_exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw $e;
		}
		catch (Exception $e){
			Connection::singleton()->transaction_rollback($link);
			throw new Internal_error_exception($e);
		}	
	}
	
}
?>